﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNQXYJ.DAL.DSSysConfigTableAdapters;

namespace HNQXYJ.DAL
{
    public  class SqlHelper
    {    
        private static string conStr = ConfigurationManager.ConnectionStrings["QXYJII"].ConnectionString;
     
        private static SqlConnection conn;

        public static string IP = "192.168.1.131";
        public static int Port = 50001;
        //连接通道属性
        public static SqlConnection Conn
        {
            get
            {
                if (conn == null || conn.State == ConnectionState.Broken)
                {
                    conn = new SqlConnection(conStr);
                }
                return conn;
            }
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sqlStr">查询语句</param>
        /// <param name="paras">参数数组</param>
        /// <returns>结果表</returns>
        public static DataTable GetDataTable(string sqlStr, params SqlParameter[] paras)
        {
            //select * from U where ic>@naaaa
            //创建命令对象(查询语句，连接通道的属性)
            SqlCommand cmd = new SqlCommand(sqlStr, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (paras != null && paras.Length > 0)
            {
                cmd.Parameters.AddRange(paras);//为命令对象添加参数数组
                //foreach (SqlParameter sp in paras)
                //{
                //    cmd.Parameters.Add(sp);
                //}
            }
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            try
            {
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                //throw ex;
                Console.WriteLine(ex.Message);
            }
            return dt;
        }

        //打开数据库连接
        public static void OpenDB()
        {
            conn.Open();
        }

        //关闭数据库连接
        public static void CloseDB()
        {
            conn.Close();
        }


        /// <summary>
        /// 非查询语句执行方法
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public static int ExecNonQuery(string sqlStr, params SqlParameter[] paras)
        {
            SqlCommand cmd = new SqlCommand(sqlStr, Conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (paras != null && paras.Length > 0)
            {
                cmd.Parameters.AddRange(paras);
            }
            OpenDB();
            int res = 0;
            try
            {
                res = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseDB();
            }
            return res;
        }

        /// <summary>
        /// 获得一个object对象的值，获取首行首列的值
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paras">参数数组</param>
        /// <returns>object</returns>
        public static object ExecuteScalar(string procName, params SqlParameter[] paras)
        {
            using (SqlConnection conn = new SqlConnection(conStr))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = procName;
                    cmd.Parameters.AddRange(paras);
                    return cmd.ExecuteScalar();
                }
            }
        }

        /// <summary>
        /// 返回一个数据读取器
        /// </summary>
        /// <param name="procName">存储过程名称</param>
        /// <param name="paras">参数数组</param>
        /// <returns>返回一个读取器</returns>
        public static SqlDataReader ExecuteDataReader(string procName, params SqlParameter[] paras)
        {
            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            using (SqlCommand cmd = conn.CreateCommand())
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = procName;
                cmd.Parameters.AddRange(paras);
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
        }

    }
}